
using Handday.Erp.Core;
using System.Collections.Generic;
using System.Threading.Tasks;
using System;
using Dapper;
using System.Linq;
using Handday.Erp.Transfer.Models.EightTradingTransfer;
using Handday.Erp.Transfer.Context;
using HandDay.Framework.Common;

namespace Handday.Erp.Transfer.Repositories.Impl
{
    /// <summary>
    /// 8项交易统计数据管理
    /// </summary>
    public class EightTradingDapperRepository : DapperRepository, IEightTradingDapperRepository
    {
        /// <summary>
        /// ctor
        /// </summary>
        public EightTradingDapperRepository(TransferDbContext context) : base(context)
        {
        }

        /// <summary>
        /// 统计8项交易数据
        /// </summary>
        /// <param name="corpId">企业编号</param>
        /// <param name="time">
        /// 时间段：key->startTime;value->endTime
        /// 顺序：今年时间段，本月时间段，上月时间段
        /// </param>
        /// <returns></returns>
        public async Task<IEnumerable<EightTradingTransferDto>> StatisticAsync(ulong corpId, Dictionary<DateTimeOffset, DateTimeOffset> time)
        {
            var sql = $@"SELECT a.CorpId,a.CustomId,a.CustomName,@year AS 'Year',@month AS 'Month' 
                ,SUM(IF(a.InputTime >= @start1 AND a.InputTime < @end1, IF(a.BillType = 3, -a.BillAmount, a.BillAmount), 0)) AS 'YearAmount' 
                ,SUM(IF(a.InputTime >= @start2 AND a.InputTime < @end2, IF(a.BillType = 3, -a.BillAmount, a.BillAmount), 0)) AS 'MonthAmount' 
                ,SUM(IF(a.InputTime >= @start3 AND a.InputTime < @end3, IF(a.BillType = 3, -a.BillAmount, a.BillAmount), 0)) AS 'LastMonthAmount' 
                ,DATE_ADD(MAX(a.InputTime),INTERVAL 8 HOUR) AS 'LastTradingTime' 
                ,COUNT(IF(a.BillType = 1, 1, NULL)) AS 'TradingCount' 
                FROM `eight_trading_source_{corpId}` a 
                GROUP BY a.CustomId ; 

                SELECT b.* FROM (
                SELECT a.CustomId,a.BillAmount AS 'LastAmount' 
                FROM `eight_trading_source_{corpId}` a 
                WHERE a.BillType = 1 
                ORDER BY a.InputTime DESC, a.BillId DESC 
                LIMIT 99999999 
                ) b 
                GROUP BY b.CustomId ; ";

            var paramters = new DynamicParameters();
            paramters.Add("@corpId", corpId);
            var i = 1;
            foreach (var item in time)
            {
                paramters.Add($"start{i}", item.Key.ToUniversalTime());
                paramters.Add($"end{i}", item.Value.ToUniversalTime());
                //设置年份月份参数
                if (i == 2)
                {
                    paramters.Add("@year", item.Key.Year);
                    paramters.Add("@month", item.Key.Month);
                }

                i++;
            }

            var grid = await DbConnection.QueryMultipleAsync(sql, paramters);
            var result = await grid.ReadAsync<EightTradingTransferDto>();
            if (result.Any())
            {
                var lastAmountList = await grid.ReadAsync<EightTradingTransferDto>();
                foreach (var item in lastAmountList)
                {
                    var transfer = result.FirstOrDefault(m => m.CustomId == item.CustomId);
                    if (transfer != null)
                    {
                        transfer.LastAmount = item.LastAmount;
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 清除数据
        /// </summary>
        public async Task ClearAsync(ulong corpId, DateTimeOffset time)
        {
            var year = time.Year;
            var month = time.Month;
            var sql = $"DELETE FROM `eight_trading_source_{corpId}` WHERE `InputTime` <= @time ; DELETE FROM `eight_trading_transfer_{corpId}` WHERE `Year` < {year} OR (`Year` = {year} AND `Month` <= {month}) ;";
            var paramters = new DynamicParameters();
            paramters.Add("@time", time.ToUniversalTime());
            var result = await DbConnection.ExecuteAsync(sql, paramters);
        }

        /// <summary>
        /// 初始化表
        /// </summary>
        /// <param name="corpId">企业编号</param>
        public async Task<bool> InitTableAsync(ulong corpId)
        {
            var utcTime = DateTimeOffset.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss");
            var suffix = corpId <= 0 ? string.Empty : $"_{corpId}";
            var sql = $@"CREATE TABLE IF NOT EXISTS `eight_trading_source{suffix}` (
              `Id` bigint unsigned NOT NULL AUTO_INCREMENT,
              `BusinessId` bigint unsigned NOT NULL COMMENT '业务编号',
              `CorpId` bigint unsigned NOT NULL COMMENT '企业编号',
              `BillType` tinyint NOT NULL COMMENT '单据类型:1.销售出库单、2.销售换货单、3.销售退货单',
              `InputTime` datetime NOT NULL COMMENT '录单时间',
              `AccountTime` datetime NOT NULL COMMENT '记账时间',
              `BillNO` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '单据编号',
              `BillId` bigint unsigned NOT NULL COMMENT '单据ID',
              `CustomId` bigint unsigned NOT NULL COMMENT '客户编号',
              `CustomName` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '客户名称',
              `BillAmount` decimal(18,8) NOT NULL COMMENT '价税合计',
              `Status` tinyint NOT NULL COMMENT '状态（0无效、1有效）',
              `CreateTime` datetime NOT NULL,
              `UpdateTime` datetime NOT NULL,
              `DeleteTime` datetime DEFAULT NULL,
              PRIMARY KEY (`Id`),
              KEY `index_0` (`CustomId`,`InputTime`) USING BTREE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='8项交易源数据';

            CREATE TABLE IF NOT EXISTS `eight_trading_transfer{suffix}` (
              `Id` bigint unsigned NOT NULL AUTO_INCREMENT,
              `BusinessId` bigint unsigned NOT NULL COMMENT '业务编号',
              `CorpId` bigint unsigned NOT NULL COMMENT '企业编号',
              `CustomId` bigint unsigned NOT NULL COMMENT '客户编号',
              `CustomName` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '客户名称',
              `Year` int NOT NULL COMMENT '年份',
              `Month` int NOT NULL COMMENT '月份',
              `YearAmount` decimal(18,8) NOT NULL COMMENT '当年金额',
              `MonthAmount` decimal(18,8) NOT NULL COMMENT '当月金额',
              `LastMonthAmount` decimal(18,8) NOT NULL COMMENT '上月金额',
              `LastAmount` decimal(18,8) NOT NULL COMMENT '上次交易金额',
              `LastTradingTime` datetime NOT NULL COMMENT '上次交易时间',
              `TradingCount` bigint unsigned NOT NULL COMMENT '交易次数',
              `Status` tinyint unsigned NOT NULL COMMENT '状态（0无效、1有效）',
              `CreateTime` datetime NOT NULL,
              `UpdateTime` datetime NOT NULL,
              `DeleteTime` datetime DEFAULT NULL,
              PRIMARY KEY (`Id`),
              KEY `index_0` (`CustomId`) USING BTREE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='8项交易传输数据'; 

            INSERT INTO `corp` (`BusinessId`, `CorpId`, `FeatureType`, `Status`, `CreateTime`, `UpdateTime`, `DeleteTime`) 
            SELECT {UniqueIdBulder.Default.NextLong()}, {corpId}, 1, 1, '{utcTime}', '{utcTime}', NULL FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `corp` WHERE `CorpId` = {corpId} AND `FeatureType` = 1) ;";

            await DbConnection.ExecuteAsync(sql);
            return true;
        }
    }
}